
library(DBI)
library(RSQLite)
library(tidyverse)

con <- dbConnect(RSQLite::SQLite(), "./data/iso_standards.sqlite")

tc_creation <- dbReadTable(con, "historical_tc_creation") %>%
  mutate(year = as.numeric(year)) %>%
  na.omit()

tc_plot1 <- tc_creation %>%
  mutate(spec = ifelse(sector %in% c("Business management and innovation", "Sustainability and environment",
                                     "Services", "Security, safety and risk"),
                       "Societal", "Technocratic")) %>%
  group_by(spec, year) %>%
  add_count(name = "Number of technical committees established") %>%
  ggplot(aes(year, spec, fill = `Number of technical committees established`)) +
  geom_tile() +
  scale_fill_gradient(low = "white", high = "darkblue") + # darkblue
  labs(x = "", y = "") +
  xlim(1945, 2025) +
  theme_classic() +
  theme(legend.position = "bottom",
        text = element_text(size = 19))

tc_plot2 <- tc_creation %>%
  filter(sector %in% c("Business management and innovation", "Sustainability and environment", 
                       "Services", "Security, safety and risk")) %>% 
  group_by(year) %>%
  add_count(name = "Number of societal technical committees established") %>%
  ggplot(aes(year, sector, fill = `Number of societal technical committees established`)) +
  geom_tile() +
  scale_fill_gradient(low = "white", high = "darkred") + # darkred
  theme_classic() +
  labs(x = "", y = "") +
  xlim(1945, 2025) +
  theme(legend.position = "bottom",
        text = element_text(size = 19)) 

cowplot::plot_grid(tc_plot1,
                   tc_plot2,
                   ncol = 1,
                   byrow = TRUE,
                   labels = "AUTO",
                   label_size = 20,
                   align = "v"
)

dbDisconnect(con)
